﻿/*
* This software is the confidential and proprietary
* information of NetBay
* Such confidential information should not be disclosed
* NetBay copyright(c) 2010
*/
#region Using
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OracleClient;
using NetBay.Core.DataAccess;
using NetBay.Core.Extensions;
using NetBay.Core.Log;
using Rio.Framework.RioManager;

#endregion

namespace Rio.Flow.DataAccess
{
    /// <summary>
    /// Class handle XXX
    /// </summary>
    public class DA_MessageFlow
    {
        #region Enums
        public enum DbFunction
        {
            [DescriptionAttribute("")]
            None,
            [DescriptionAttribute("P_MESSAGE_FLOW_Get_BySenderId")]
            GetMessageFlowSentByMe,
            [DescriptionAttribute("P_MESSAGE_FLOW_Get_ByRecId")]
            GetMessageFlowInMyBox,
            [DescriptionAttribute("P_MESSAGE_FLOW_Add")]
            AddMessageFlow,
            [DescriptionAttribute("P_MESSAGE_FLOW_Histo_ByGroup")]
            GetHistoryByGroupMessageFlow,
            [DescriptionAttribute("P_MESSAGE_FLOW_Histo_ByUserGrp")]
            GetHistoryByUserGroupsMessageFlow,
            [DescriptionAttribute("P_MESSAGE_FLOW_Histo_ByUser")]
            GetHistoryByUserMessageFlow,
            [DescriptionAttribute("P_MESSAGE_FLOW_Delete")]
            DeleteMessageFlow,
            [DescriptionAttribute("P_MESSAGE_FLOW_Update")]
            UpdateMessageFlow,
            [DescriptionAttribute("P_MESSAGE_FLOW_USER_Delete")]
            DeleteMessageFlowUser,
            [DescriptionAttribute("P_DOC_ETAT_List")]
            GetStateDocList,
            [DescriptionAttribute("P_DOC_ETAT_Group_Update")]
            UpdateStateDocList,
            [DescriptionAttribute("P_CHRONO_Request")]
            RequestChrono,
            [DescriptionAttribute("P_CHRONO_Update")]
            UpdateChrono,
            [DescriptionAttribute("P_REMIND_FLOW_Add")]
            AddReminderFlow,
            [DescriptionAttribute("P_MESSAGE_FLOW_Document_Add")]
            AddDocumentToMessageFlow,
            [DescriptionAttribute("P_MESSAGE_FLOW_Get_ById")]
            GetMessageFlowById,
            [DescriptionAttribute("P_MESSAGE_FLOW_ByDocID")]
            GetMessageFlowByDocId,
            [DescriptionAttribute("P_MESSAGE_FLOW_Add_Senders")]
            AddSendersToMessageFlow,

            [DescriptionAttribute("P_MESSAGE_FLOW_CNT_BySenderId")]
            CountMessageFlowSentByMe,
            [DescriptionAttribute("P_MESSAGE_FLOW_CNT_ByRecId")]
            CountMessageFlowInMyBox,
        }
        #endregion

        #region Functions

        #region Publics

        /// <summary>
        /// Add a document to a message Flow
        /// </summary>
        /// <param name="p_lMessageFlowId">The message flow identifier</param>
        /// <param name="p_lDocumentId">The document identifier</param>
        public static void AddDocumentToMessageFlow(long p_lMessageFlowId, long p_lDocumentId)
        {
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.AddDocumentToMessageFlow.szGetDescription());
            db.AddInParameter(dbCommand, "p_lMessageFlowId", DbType.String, p_lMessageFlowId);
            db.AddInParameter(dbCommand, "p_lDocId", DbType.String, p_lDocumentId);

            db.ExecuteScalar(dbCommand);
        }

        /// <summary>
        /// Get a message flow by its id
        /// </summary>
        /// <param name="p_lMessageFlowId">The message flow identifier</param>
        public static IDataReader GetMessageFlowById(long p_lMessageFlowId)
        {
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetMessageFlowById.szGetDescription());
            db.AddInParameter(dbCommand, "p_lMessageFlowId", DbType.Int32, p_lMessageFlowId);

            return db.ExecuteReader(dbCommand);
        }

        public static long CountMessageFlowSentByMe(long p_lUserId)
        {
            LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", (DbFunction.CountMessageFlowSentByMe.szGetDescription()));
            LogManager.Instance().LogDebug("(p_SenderId<" + p_lUserId + ">)");

            var db = DbHelper.DataBase;
            long result = 0;
            using (var dbCommand = db.GetNewCommand((DbFunction.CountMessageFlowSentByMe.szGetDescription())))
            {
                db.AddInParameter(dbCommand, "p_SenderId", DbType.Int64, p_lUserId);

                using (IDataReader reader = db.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        result = reader.GetLongValue("MessagesCount");
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// Retrive al messages sent by a user
        /// </summary>
        /// <param name="p_lUserId">User identifier</param>
        /// <returns>all messages sent by the users</returns>
        public static IDataReader GetMessageFlowSentByMe(long p_lUserId)
        {
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetMessageFlowSentByMe.szGetDescription());
            db.AddInParameter(dbCommand, "p_SenderId", DbType.String, p_lUserId);

            return db.ExecuteReader(dbCommand);
        }

        public static long CountMessageFlowInMyBox(long p_lUserId, long p_iDocState)
        {
            LogManager.Instance().LogDebugFormat("Calling SP: '{0}'", (DbFunction.CountMessageFlowInMyBox.szGetDescription()));
            LogManager.Instance().LogDebug("(p_ReceiverId<" + p_lUserId + "> p_iDocState<" + p_iDocState + ">)");

            var db = DbHelper.DataBase;
            long result = 0;
            using (var dbCommand = db.GetNewCommand((DbFunction.CountMessageFlowInMyBox.szGetDescription())))
            {
                db.AddInParameter(dbCommand, "p_ReceiverId", DbType.Int64, p_lUserId);
                db.AddInParameter(dbCommand, "p_iDocState", DbType.Int64, p_iDocState);

                using (IDataReader reader = db.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        result = reader.GetLongValue("MessagesCount");
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="p_lUserId">User identifier</param>
        /// <returns>all messages in the users inbox</returns>
        public static IDataReader GetMessageFlowInMyBox(long p_lUserId)
        {
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetMessageFlowInMyBox.szGetDescription());
            db.AddInParameter(dbCommand, "p_ReceiverId", DbType.String, p_lUserId);

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Add a mssage Fow
        /// </summary>
        /// <param name="p_lEmetteurId">Identifier of the source user</param>
        /// <param name="p_lChronoId">Identifier of the chrono </param>
        /// <param name="p_lDocId">Document Identifier</param>
        /// <param name="p_sObjet">Object</param>
        /// <param name="p_sMemo">Text</param>
        /// <param name="p_sGroupDestinataire">All destination group seperate with a ";"</param>
        /// <param name="p_bSendAlertByMail">If an alert must be sent by mail</param>
        /// <returns>The identifier of the message flow</returns>
        public static long AddMessageFlow(long p_lEmetteurId, long p_lChronoId, long p_lDocId, string p_sObjet, string p_sMemo, string p_sGroupDestinataire, Boolean p_bSendAlertByMail, string p_sFolderURL)
        {
            long l_lMessageFlowId = 0;
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.AddMessageFlow.szGetDescription());
            db.AddInParameter(dbCommand, "p_lEmetteurId", DbType.Int64, p_lEmetteurId);
            db.AddInParameter(dbCommand, "p_lChronoId", DbType.Int64, p_lChronoId);
            db.AddInParameter(dbCommand, "p_lDocId", DbType.Int64, p_lDocId);
            db.AddInParameter(dbCommand, "p_sObjet", DbType.String, p_sObjet);
            db.AddInParameter(dbCommand, "p_sMemo", DbType.String, p_sMemo);
            db.AddInParameter(dbCommand, "p_sGroupDestinataire", DbType.String, p_sGroupDestinataire);
            db.AddInParameter(dbCommand, "p_bSendAlertByMail", DbType.Int32, Convert.ToInt32(p_bSendAlertByMail));
            db.AddInParameter(dbCommand, "p_sFolderUrl", DbType.String, p_sFolderURL);

            IDataReader l_oMessageFlowDataReader = db.ExecuteReader(dbCommand);
            using (l_oMessageFlowDataReader)
            {
                while (l_oMessageFlowDataReader.Read())
                {
                    l_lMessageFlowId = Convert.ToInt64(l_oMessageFlowDataReader["MESSAGE_FLOW_ID"]);
                }
            }

            return l_lMessageFlowId;
        }

        /// <summary>
        /// Add a reminder on a message Flow
        /// </summary>
        /// <param name="p_lUserId">Identifier of the source user</param>
        /// <param name="p_lMessageFowId">Identifier of the Message flow</param>
        /// <param name="p_sGrpId">Group Identifier</param>
        public static void AddMessageFlowReminder(long p_lUserId, long p_lMessageFowId, long p_sGrpId)
        {
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.AddReminderFlow.szGetDescription());
            db.AddInParameter(dbCommand, "p_lUserId", DbType.Int32, p_lUserId);
            db.AddInParameter(dbCommand, "p_lMessageFowId", DbType.Int32, p_lMessageFowId);
            db.AddInParameter(dbCommand, "p_sGrpId", DbType.Int32, p_sGrpId);

            db.ExecuteScalar(dbCommand);
        }

        /// <summary>
        /// Retreive historical message flow by an user
        /// </summary>
        /// <param name="p_lUserId">User ID</param>
        /// <param name="p_lDocumentId">Document ID</param>
        /// <param name="p_lMsgFlowId">MessageFlow ID</param>
        /// <returns>DataReader with historical</returns>
        public static IDataReader GetHistoryByUserMessageFlow(long p_lUserId, long p_lDocumentId, long p_lMsgFlowId)
        {
            NetBay.Core.Log.LogManager.Instance().LogDebug("(p_lUserId<" + p_lUserId + "> p_lDocumentId<" + p_lDocumentId + "> p_lMsgFlowId<" + p_lMsgFlowId + ">)...");
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetHistoryByUserMessageFlow.szGetDescription());
            db.AddInParameter(dbCommand, "p_lUserId", DbType.String, p_lUserId);
            db.AddInParameter(dbCommand, "p_lDocId", DbType.String, p_lDocumentId);
            db.AddInParameter(dbCommand, "p_lMsgFlowId", DbType.String, p_lMsgFlowId);

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Retreive historical message flow by a group
        /// </summary>
        /// <param name="p_lGroupId">Group ID</param>
        /// <param name="p_lDocumentId">Document ID</param>
        /// <param name="p_lMsgFlowId">MessageFlow ID</param>
        /// <returns>DataReader with historical</returns>
        public static IDataReader GetHistoryByGroupMessageFlow(long p_lGroupId, long p_lDocumentId, long p_lMsgFlowId)
        {
            NetBay.Core.Log.LogManager.Instance().LogDebug("(p_lGroupId<" + p_lGroupId + "> p_lDocumentId<" + p_lDocumentId + "> p_lMsgFlowId<" + p_lMsgFlowId + ">)...");
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetHistoryByGroupMessageFlow.szGetDescription());
            db.AddInParameter(dbCommand, "p_lGroupId", DbType.Int32, p_lGroupId);
            db.AddInParameter(dbCommand, "p_lDocId", DbType.String, p_lDocumentId);
            db.AddInParameter(dbCommand, "p_lMsgFlowId", DbType.String, p_lMsgFlowId);

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Retreive historical message flow for al user's group
        /// </summary>
        /// <param name="p_lUserId">User ID</param>
        /// <param name="p_lDocumentId">Document ID</param>
        /// <param name="p_lMsgFlowId">MessageFlow ID</param>
        /// <returns>DataReader with historical</returns>
        public static IDataReader GetHistoryByUserGroupsMessageFlow(long p_lUserId, long p_lDocumentId, long p_lMsgFlowId, bool p_bIsAdmin)
        {
            NetBay.Core.Log.LogManager.Instance().LogDebug("(p_lUserId<" + p_lUserId + "> p_lDocumentId<" + p_lDocumentId + ">)...");
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetHistoryByUserGroupsMessageFlow.szGetDescription());
            db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, p_lUserId);
            db.AddInParameter(dbCommand, "p_lDocId", DbType.Int64, p_lDocumentId);
            db.AddInParameter(dbCommand, "p_lMsgFlowId", DbType.Int64, p_lMsgFlowId);
            db.AddInParameter(dbCommand, "p_bIsAdmin", DbType.Int32, Convert.ToInt32(p_bIsAdmin));

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Retreive all doc state
        /// </summary>
        /// <returns>DataReader with doc state</returns>
        public static IDataReader GetStateDocList()
        {
            NetBay.Core.Log.LogManager.Instance().LogDebug("...");
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetStateDocList.szGetDescription());

            return db.ExecuteReader(dbCommand);
        }

        /// <summary>
        /// Update the doc status
        /// </summary>
        /// <param name="p_lDocumentId">Document ID</param> 
        /// <param name="p_lDocEtatId">StateDoc ID</param>
        /// <param name="p_lMsgFlowId">MsgFlow ID</param>
        /// <param name="p_lUserId">User ID</param>
        /// <param name="p_lGroupId">Group ID</param>
        /// <returns>true (a update has been done) otherwise false and email of owner of flow</returns>
        public static Tuple<bool, string> UpdateStateDocList(long p_lDocumentId, long p_lDocEtatId, long p_lMsgFlowId, long p_lUserId, List<long> p_lGroupIdList, string p_sDocEtatGroupComment)
        {
            var l_tRet = Tuple.Create(false, string.Empty);
            var db = DbHelper.DataBase;

            IDataReader l_oMessageFlowDataReader;
            foreach (var l_oGroup in p_lGroupIdList)
            {

                var dbCommand = db.GetNewCommand(DbFunction.UpdateStateDocList.szGetDescription());
                db.AddInParameter(dbCommand, "p_lDocumentId", DbType.Int64, p_lDocumentId);
                db.AddInParameter(dbCommand, "p_lDocEtatId", DbType.Int64, p_lDocEtatId);
                db.AddInParameter(dbCommand, "p_lMsgFlowId", DbType.Int64, p_lMsgFlowId);
                db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, p_lUserId);
                db.AddInParameter(dbCommand, "p_sDocEtatGroupComment", DbType.String, p_sDocEtatGroupComment);
                db.AddInParameter(dbCommand, "p_lGroupId", DbType.Int64, l_oGroup);

                l_oMessageFlowDataReader = db.ExecuteReader(dbCommand);
                using (l_oMessageFlowDataReader)
                {
                    while (l_oMessageFlowDataReader.Read())
                    {
                        var l_bRet = Convert.ToBoolean(Convert.ToInt16(l_oMessageFlowDataReader["Inserted"]));
                        var l_szRet = l_oMessageFlowDataReader["ruser_email"].ToString();
                        l_tRet = Tuple.Create(l_bRet, l_szRet);
                    }
                }
            }
            return l_tRet;
        }

        /// <summary>
        /// Delete a list msg flow
        /// </summary>
        /// <param name="p_oMsgFlowIds">List of identifier of message flow</param>
        public static void DeleteMessageFlow(List<long> p_oMsgFlowIds)
        {
            var db = DbHelper.DataBase;

            var l_szMsgFlowIds = string.Empty;
            foreach (var l_lItem in p_oMsgFlowIds)
            {
                l_szMsgFlowIds += l_lItem + ";";
            }

            if (string.IsNullOrEmpty(l_szMsgFlowIds) == false)
            {
                var dbCommand = db.GetNewCommand(DbFunction.DeleteMessageFlow.szGetDescription(), false);
                db.AddInParameter(dbCommand, "p_sMessageFlowIds", DbType.String, l_szMsgFlowIds);
                db.ExecuteNonQuery(dbCommand);
            }
        }

        /// <summary>
        /// Delete list msg flow from user
        /// </summary>
        /// <param name="p_oMsgFlowId">List of msg flow ID</param>
        /// <param name="p_oRguId">List of Rguid</param>
        /// <returns></returns>
        public static void DeleteMessageFlowUser(List<long> p_oMsgFlowId, List<long> p_oRguId)
        {
            var db = DbHelper.DataBase;


            var dbCommand = db.GetNewCommand(DbFunction.DeleteMessageFlowUser.szGetDescription(), false);
            var l_szMsgFlowRguIds = string.Empty;

            var l_oEnumeratorFlowId = p_oMsgFlowId.GetEnumerator();
            var l_oEnumeratorRguId = p_oRguId.GetEnumerator();

            for (var i = 0; i < p_oMsgFlowId.Count; i++)
            {
                // Check end loop
                if (l_oEnumeratorFlowId.MoveNext() == false)
                    break;
                if (l_oEnumeratorRguId.MoveNext() == false)
                    break;
                l_szMsgFlowRguIds += l_oEnumeratorFlowId.Current + ";" + l_oEnumeratorRguId.Current + ";" + "#";
            }

            if (string.IsNullOrEmpty(l_szMsgFlowRguIds) == false)
            {
                db.AddInParameter(dbCommand, "p_sMessageFlowRguIds", DbType.String, l_szMsgFlowRguIds);
                db.ExecuteNonQuery(dbCommand);
            }
        }

        /// <summary>
        /// Request a valid chrono
        /// </summary>
        /// <param name="p_szPrefixChrono">Prefix chrono</param>
        /// <param name="p_lChronoTypeId">Chrono type identifier</param>
        /// <param name="p_iCreationVolee">0(needed to create) otherwise 1</param>
        /// <returns>Couple of (ChronoId, ChronoWording)</returns>
        public static Tuple<long, string> RequestChrono(string p_szPrefixChrono, long p_lChronoTypeId, int p_iCreationVolee, long p_lUserId)
        {
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.RequestChrono.szGetDescription());
            db.AddInParameter(dbCommand, "p_sChronoPrefixe", DbType.String, p_szPrefixChrono);
            db.AddInParameter(dbCommand, "p_lChronoTypeId", DbType.Int64, p_lChronoTypeId);
            db.AddInParameter(dbCommand, "p_iCreationVolee", DbType.Int32, p_iCreationVolee);
            db.AddInParameter(dbCommand, "p_lUserId", DbType.Int64, p_lUserId);

            IDataReader l_oChronoDataReader = db.ExecuteReader(dbCommand);
            long l_lChronoId = -1;
            var l_szChronoWordind = string.Empty;
            while (l_oChronoDataReader.Read()) 
            {
                l_lChronoId = Convert.ToInt64(l_oChronoDataReader["CHRONO_ID"]);
                l_szChronoWordind = l_oChronoDataReader["CHRONO_LIBELLE"].ToString();
            }

            return Tuple.Create(l_lChronoId, l_szChronoWordind);
        }

        /// <summary>
        /// Update the status of chrono
        /// </summary>
        /// <param name="p_lChronoEtatId">Chrono state identifier</param>
        public static void UpdateChrono(string p_lChronoId, long p_lChronoEtatId)
        {
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.UpdateChrono.szGetDescription(), false);
            db.AddInParameter(dbCommand, "p_lChronoLibelle", DbType.String, p_lChronoId);
            db.AddInParameter(dbCommand, "p_lChronoEtatId", DbType.Int64, p_lChronoEtatId);

            db.ExecuteNonQuery(dbCommand);
        }

        /// <summary>
        /// Update the MSG_FLOW_LAST_READ_DATE field of the message flow table in order to be 
        /// notified when a new action is done on the message
        /// </summary>
        /// <param name="p_lMessageFlowId"></param>
        public static void UpdateMessageFlowLastRead(long p_lMessageFlowId)
        {
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.UpdateMessageFlow.szGetDescription(), false);
            db.AddInParameter(dbCommand, "p_lMessageFlowId", DbType.Int64, p_lMessageFlowId);
            db.AddInParameter(dbCommand, "p_dtLastReadDate", DbType.DateTime, DateTime.Now);            

            db.ExecuteNonQuery(dbCommand);
        }

        /// <summary>
        /// Get all the message flow for a doc id
        /// </summary>
        /// <param name="p_lDocId"></param>
        /// <returns></returns>
        public static IDataReader GetMessageFlowIDByDocId(long p_lDocId)
        {
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.GetMessageFlowByDocId.szGetDescription());
            db.AddInParameter(dbCommand, "p_lDocId", DbType.Int64, p_lDocId);

            return db.ExecuteReader(dbCommand);
        }

        public static void AddSenderToMessageFlow(List<long> p_lSenders, long p_lMessageFlowId)
        {
            var db = DbHelper.DataBase;

            var dbCommand = db.GetNewCommand(DbFunction.AddSendersToMessageFlow.szGetDescription(), false);
            db.AddInParameter(dbCommand, "p_MsgflowId", DbType.Int64, p_lMessageFlowId);
            db.AddInParameter(dbCommand, "p_sMessageFlowRgIds", DbType.String, string.Join(";", p_lSenders) + ";");

            db.ExecuteScalar(dbCommand);
        }
        #endregion

        #endregion
    }
}